﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using Module.Common.Models;
using Module.Core.EntitiesInfo.TablesInfo;

namespace Module.Core.DAO.DAO
{
  public  class NewEmailDAO
    {
        #region Fields

        protected string connectionStringName;

        #endregion	
     
        /// <summary>
        /// Saves a record to the NewEmails table.
        /// </summary>
        public virtual int Insert(NewsEmailInfo emailInfo)
        {
            SqlParameter[] parameters = new SqlParameter[]
			{
				new SqlParameter("@IdMember", emailInfo.IdMember),
				new SqlParameter("@Title", emailInfo.Title),
				new SqlParameter("@ContentEmail", emailInfo.ContentEmail),
				new SqlParameter("@Status", emailInfo.Status),
				new SqlParameter("@EmailGet", emailInfo.EmailGet),
                new SqlParameter("@EmailTransfer", emailInfo.EmailTransfer),
                new SqlParameter("@CreatedDate", emailInfo.CreatedDate)
			};

            return (int)SqlClientUtility.ExecuteScalar(connectionStringName, CommandType.StoredProcedure, "NewEmail_Insert", parameters);
        }
  
      //search All

        public DataSet Search_All(string title, string emailTransfer, DateTime createdOnFrom, DateTime createdOnTo)
        {
            SqlParameter[] parameters = new SqlParameter[]
                         {
                            
                                   new SqlParameter("@Title", title),
                                   new SqlParameter("@EmailTransfer", emailTransfer),
                                   new SqlParameter("@CreatedOnFrom", createdOnFrom),
                                   new SqlParameter("@CreatedOnTo", createdOnTo)
                        
                         };
            using (DataSet ds = SqlClientUtility.ExecuteDataSet(connectionStringName, CommandType.StoredProcedure, "NewEmail_SearchALL", parameters))
            {
                if (ds.Tables[0].Rows.Count > 0)
                    return ds;
                return null;
            }
        }

        

        public virtual void Delete(int idEmails)
        {
            SqlParameter[] parameters = new SqlParameter[]
			{
				new SqlParameter("@IDEmails", idEmails)
			};

            SqlClientUtility.ExecuteNonQuery(connectionStringName, CommandType.StoredProcedure, "NewEmails_Delete", parameters);
        }

      //Lấy tổng số bản ghi
        public int GetCount(DateTime createOnFrom, DateTime createOnTo)
        {
            SqlParameter[] parameters = new SqlParameter[]
                                            {
                                                new SqlParameter("@CreatedOnFrom",createOnFrom),
                                                new SqlParameter("@CreatedOnTo",createOnTo)                                               
                                            };
            return
                (int)SqlClientUtility.ExecuteScalar(connectionStringName, CommandType.StoredProcedure, "NewEmail_GetCount", parameters);

        }


        public virtual int SelectCount(bool isTotalRow)
        {
            SqlParameter[] parameters = new SqlParameter[]
			{           
				new SqlParameter("@isTotalRow", true.ToString())
			};

            return (int)SqlClientUtility.ExecuteScalar(connectionStringName, CommandType.StoredProcedure, "Sp_NewEmail_Search", parameters);
        }

        public DataSet ListNewEmailPage(int idEmails, DateTime createOnFrom, DateTime createOnTo, int currPage, int recodperPage, int pageSize, int total, string link)
        {
            SqlParameter[] parameters = new SqlParameter[]
                                          {
                                              new SqlParameter("@IDEmails", idEmails),
                                              new SqlParameter("@CreatedOnFrom",createOnFrom),
                                              new SqlParameter("@CreatedOnTo",createOnTo),
                                              new SqlParameter("@currPage", currPage),
                                              new SqlParameter("@recodperPage", recodperPage),
                                              new SqlParameter("@pageSize", pageSize), 
                                              new SqlParameter("@total", total),
                                              new SqlParameter("@link", link)
                                          };
            using (DataSet ds = SqlClientUtility.ExecuteDataSet(connectionStringName, CommandType.StoredProcedure, "NewEmail_search_Page", parameters))
            {
                if (ds.Tables[0].Rows.Count > 0)
                    return ds;
                return null;
            }
        }

        public virtual DataTable Search(int beginRow, int endRow, string orderByColumn)
        {
            SqlParameter[] parameters = new SqlParameter[]
			{
                new SqlParameter("@BeginRow", beginRow),
                new SqlParameter("@EndRow", endRow),
                new SqlParameter("@OrderByColumn", orderByColumn),
				new SqlParameter("@isTotalRow", false.ToString())
			};

            return SqlClientUtility.ExecuteDataTable(connectionStringName, CommandType.StoredProcedure, "Sp_NewEmail_Search", parameters);
        }
      //Hiện thị danh sách hộp thư đến
        public DataSet SelectLstInbox(string email,int param)
        {
            SqlParameter[] parameters = new SqlParameter[]
                                          {
                                              new SqlParameter("@Email", email),
                                              new SqlParameter("@param", param)
                                             
                                          };
            using (DataSet ds = SqlClientUtility.ExecuteDataSet(connectionStringName, CommandType.StoredProcedure, "NewEmail_LstInbox", parameters))
            {
                if (ds.Tables[0].Rows.Count > 0)
                    return ds;
                return null;
            }
        }

        
        public virtual NewsEmailInfo Select(int status)
        {
            SqlParameter[] parameters = new SqlParameter[]
			{
                 new SqlParameter("@Status", status)
				
               
			};
            try
            {
                using (SqlDataReader dataReader = SqlClientUtility.ExecuteReader(connectionStringName, CommandType.StoredProcedure, "NewEmail_SelectAll", parameters))
                {
                    if (dataReader.Read())
                    {
                        return MakeNewMailInfo(dataReader);
                    }
                    else
                    {
                        return null;
                    }
                }
            }
            catch (Exception)
            {
            }
            return null;
        }
      //phân trang hộp thư

        public DataSet ListInbox(string email, int currPage, int recodperPage, int pageSize,int param)
        {
            SqlParameter[] parameters = new SqlParameter[]
                                          {                                              
                                             
                                              new SqlParameter("@currPage",currPage),
                                              new SqlParameter("@recodperpage",recodperPage),
                                              new SqlParameter("@Pagesize",pageSize),
                                              new SqlParameter("@Email",email),
                                               new SqlParameter("@param",param)
                                             
                                          };
            using (DataSet ds = SqlClientUtility.ExecuteDataSet(connectionStringName, CommandType.StoredProcedure, "NewEmail_PagingByNewEmails", parameters))
            {
                if (ds.Tables[0].Rows.Count > 0)
                    return ds;
                return null;
            }
        }
      //Chi tiết thư
        public virtual NewsEmailInfo DetailsInbox(int idEmail, string email, int param)
        {
            SqlParameter[] parameters = new SqlParameter[]
			{
                new SqlParameter("@IDEmails",idEmail),
				new SqlParameter("@Email",email),
                new SqlParameter("@param",param)
               
			};

            using (SqlDataReader dataReader = SqlClientUtility.ExecuteReader(connectionStringName, CommandType.StoredProcedure, "NewEmail_LstInbox", parameters))
            {
                if (dataReader.Read())
                {
                    return MakeNewMailInfo(dataReader);
                }
                else
                {
                    return null;
                }
            }
        }
        /// <summary>
        /// Cập nhật status khi thành viên đọc thư
        /// status=1-> -Thư chưa đọc
        /// status=2  -Thư đã đọc
        /// 
        /// </summary>
        public virtual void UpdateStatus(int idEmails, int status)
        {
            SqlParameter[] parameters = new SqlParameter[]
			{
				new SqlParameter("@IDEmails", idEmails),
                new SqlParameter("@Status", status),
			};

            SqlClientUtility.ExecuteNonQuery(connectionStringName, CommandType.StoredProcedure, "NewEmails_UpdateStatus", parameters);
        }

      //Lấy tổng số bản ghi của hộp thư đến trạng thái chưa đọc

        public int CountInbox(string email,int status)
        {
            SqlParameter[] parameters = new SqlParameter[]
                                            {
                                                new SqlParameter("@Email",email),
                                                new SqlParameter("@Status",status)
                                                                                          
                                            };
            return
                (int)SqlClientUtility.ExecuteScalar(connectionStringName, CommandType.StoredProcedure, "NewEmail_CountInbox", parameters);

        }

        public virtual NewsEmailInfo ReplyEmail(int idEmails)
        {
            SqlParameter[] parameters = new SqlParameter[]
			{
                 new SqlParameter("@IDEmails", idEmails)
				
               
			};
            try
            {
                using (SqlDataReader dataReader = SqlClientUtility.ExecuteReader(connectionStringName, CommandType.StoredProcedure, "NewEmail_Select", parameters))
                {
                    if (dataReader.Read())
                    {
                        return MakeNewMailInfo(dataReader);
                    }
                    else
                    {
                        return null;
                    }
                }
            }
            catch (Exception)
            {
            }
            return null;
        }
        /// <summary>
        /// Creates a new instance of the NewEmails class and populates it with data from the specified SqlDataReader.
        /// </summary>
        protected virtual NewsEmailInfo MakeNewMailInfo(SqlDataReader dataReader)
        {
            NewsEmailInfo newEmailInfo = new NewsEmailInfo();
            newEmailInfo.IDEmails = SqlClientUtility.GetInt32(dataReader, DbConstants.NEWEMAIL.IDEMAILS, 0);
            newEmailInfo.IdMember = SqlClientUtility.GetInt32(dataReader, DbConstants.NEWEMAIL.IDMEMBER, 0);
            newEmailInfo.Title = SqlClientUtility.GetString(dataReader, DbConstants.NEWEMAIL.TITLE, String.Empty);
            newEmailInfo.ContentEmail = SqlClientUtility.GetString(dataReader, DbConstants.NEWEMAIL.CONTENTEMAIL, String.Empty);
            newEmailInfo.EmailGet = SqlClientUtility.GetString(dataReader, DbConstants.NEWEMAIL.EMAILGET, String.Empty);
            newEmailInfo.EmailTransfer = SqlClientUtility.GetString(dataReader, DbConstants.NEWEMAIL.EMAILTRANSFER, String.Empty);
            newEmailInfo.Status = SqlClientUtility.GetInt32(dataReader, DbConstants.NEWEMAIL.STATUS, 0);
            newEmailInfo.CreatedDate = SqlClientUtility.GetDateTime(dataReader, DbConstants.NEWEMAIL.CREATEDATE, DateTime.Now);

            return newEmailInfo;
        }

    }
}
